package com.site.dao.impl;

import com.site.dao.CableSizeDao;
import com.site.pojo.CableSize;
import com.site.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * FileName: CableSizeDaoImpl.java
 * 电缆规格表DaoImpl
 *
 * @author 田尚忠
 * @Date    2021/11/28
 * @version 1.00
 */
public class CableSizeDaoImpl implements CableSizeDao {

    //查询全部
    @Override
    public List<CableSize> selectAll() throws SQLException {
        Connection conn = JDBCUtils.getConnection();
        List<CableSize> list = new ArrayList<>();;
        String sql = "SELECT `typeid`, `id`, `goods_id`, `with`, `height`, `length`, `voltage`, `describe` FROM `cable_size`";
        ResultSet rs = JDBCUtils.executeQuery(conn,sql);
        while (rs.next()){
            CableSize cableSize = new CableSize();
            cableSize.setTypeid(rs.getLong("typeid"));
            cableSize.setId(rs.getLong("id"));
            cableSize.setGoodsId(rs.getLong("goods_id"));
            cableSize.setWith(rs.getString("with"));
            cableSize.setHeight(rs.getString("height"));
            cableSize.setLength(rs.getString("length"));
            cableSize.setVoltage(rs.getString("voltage"));
            cableSize.setDescribe(rs.getString("describe"));
            list.add(cableSize);
        }
        JDBCUtils.close(rs,null,conn);
        return list;
    }

    //按照id查询
    @Override
    public CableSize selectByid(int id) throws SQLException {
        CableSize cableSize = new CableSize();
        Connection conn = JDBCUtils.getConnection();
        String sql = "SELECT `typeid`, `id`, `goods_id`, `with`, `height`, `length`, `voltage`, `describe` FROM `cable_size` WHERE id=?";
        ResultSet rs = JDBCUtils.executeQuery(conn,sql,id);
        while (rs.next()){
            cableSize.setTypeid(rs.getLong("typeid"));
            cableSize.setId(rs.getLong("id"));
            cableSize.setGoodsId(rs.getLong("goods_id"));
            cableSize.setWith(rs.getString("with"));
            cableSize.setHeight(rs.getString("height"));
            cableSize.setLength(rs.getString("length"));
            cableSize.setVoltage(rs.getString("voltage"));
            cableSize.setDescribe(rs.getString("describe"));
        }
        JDBCUtils.close(rs,null,conn);
        return cableSize;
    }

    //按照信息查询,是否含有该商品,新增的时候需要用到
    @Override
    public CableSize selectByinfo(CableSize cableSize1) throws SQLException {
        CableSize cableSize = new CableSize();
        Connection conn = JDBCUtils.getConnection();
        String sql = "SELECT * FROM cable_size WHERE typeid=? AND goods_id = ? AND `with`=? AND height=? AND length=? AND voltage=?";
        ResultSet rs = JDBCUtils.executeQuery(conn,sql,cableSize1.getTypeid(),cableSize1.getGoodsId(),cableSize1.getWith(),cableSize1.getHeight(),cableSize1.getLength(),cableSize1.getVoltage());
        while (rs.next()){
            cableSize.setTypeid(rs.getLong("typeid"));
            cableSize.setId(rs.getLong("id"));
            cableSize.setGoodsId(rs.getLong("goods_id"));
            cableSize.setWith(rs.getString("with"));
            cableSize.setHeight(rs.getString("height"));
            cableSize.setLength(rs.getString("length"));
            cableSize.setVoltage(rs.getString("voltage"));
            cableSize.setDescribe(rs.getString("describe"));
            break;
        }
        JDBCUtils.close(rs,null,conn);
        return cableSize;
    }

    //按照商品id查询
    @Override
    public List<CableSize> selectByGoodsid(int id) throws SQLException {
        Connection conn = JDBCUtils.getConnection();
        List<CableSize> list = new ArrayList<>();;
        String sql = "SELECT `typeid`, `id`, `goods_id`, `with`, `height`, `length`, `voltage`, `describe` FROM `cable_size` WHERE goods_id=?";
        ResultSet rs = JDBCUtils.executeQuery(conn,sql,id);
        while (rs.next()){
            CableSize cableSize = new CableSize();
            cableSize.setTypeid(rs.getLong("typeid"));
            cableSize.setId(rs.getLong("id"));
            cableSize.setGoodsId(rs.getLong("goods_id"));
            cableSize.setWith(rs.getString("with"));
            cableSize.setHeight(rs.getString("height"));
            cableSize.setLength(rs.getString("length"));
            cableSize.setVoltage(rs.getString("voltage"));
            cableSize.setDescribe(rs.getString("describe"));
            list.add(cableSize);
        }
        JDBCUtils.close(rs,null,conn);
        return list;
    }

    //新增
    @Override
    public int addCableSize(CableSize cableSize1) throws SQLException {
        Connection conn = JDBCUtils.getConnection();
        String sql = "INSERT INTO `hurricane_house`.`cable_size` (`typeid`,`goods_id`, `with`, `height`, `length`, `voltage`, `describe`) VALUES (?,?, ?, ?, ?, ?, ?);";
        Object [] params = {cableSize1.getTypeid(),cableSize1.getGoodsId(),cableSize1.getWith(),cableSize1.getHeight(),cableSize1.getLength(),cableSize1.getVoltage(),cableSize1.getDescribe()};
        int i = JDBCUtils.executeUpdate(sql, params);
        JDBCUtils.close(null,conn);
        return i;
    }

    //按照id进行删除
    @Override
    public int delCableSizeByid(int id) throws SQLException {
        String sql = "DELETE FROM cable_size WHERE id = ?";
        Object [] params = {id};
        return JDBCUtils.executeUpdate(sql, params);
    }

    //有事务的新增
    @Override
    public int addCableSize(Connection conn, CableSize cableSize1) throws SQLException {
        String sql = "INSERT INTO `hurricane_house`.`cable_size` (`typeid`,`goods_id`, `with`, `height`, `length`, `voltage`, `describe`) VALUES (?,?, ?, ?, ?, ?, ?);";
        Object [] params = {cableSize1.getTypeid(),cableSize1.getGoodsId(),cableSize1.getWith(),cableSize1.getHeight(),cableSize1.getLength(),cableSize1.getVoltage(),cableSize1.getDescribe()};
        return JDBCUtils.executeUpdate(conn,sql, params);
    }
    //有事务的删除
    @Override
    public int delCableSizeByid(Connection conn, int id) throws SQLException {
        String sql = "DELETE FROM cable_size WHERE id = ?";
        Object [] params = {id};
        return JDBCUtils.executeUpdate(conn, sql, params);
    }

    //按照商品id删除
    @Override
    public int delCableSizeByGoodsid(int id) throws SQLException {
        String sql = "DELETE FROM cable_size WHERE goods_id = ?";
        Object [] params = {id};
        return JDBCUtils.executeUpdate(sql, params);
    }

    //有事务按照商品id删除
    @Override
    public int delCableSizeByGoodsid(Connection conn, int id) throws SQLException {
        String sql = "DELETE FROM cable_size WHERE goods_id = ?";
        Object [] params = {id};
        return JDBCUtils.executeUpdate(conn, sql, params);
    }

    //按照id修改数据
    @Override
    public int updateCableSize(int id,CableSize cableSize1) throws SQLException {
        String sql = "UPDATE `hurricane_house`.`cable_size` SET `goods_id` = ?, `with` = ?, `height` = ?, `length` = ?, `voltage` = ?, `describe` = ? WHERE `id` = ?;";
        Object [] params = {cableSize1.getGoodsId(),cableSize1.getWith(),cableSize1.getHeight(),cableSize1.getLength(),cableSize1.getVoltage(),cableSize1.getDescribe(),id};
        return JDBCUtils.executeUpdate(sql, params);
    }

    @Override
    public int updateCableSize(Connection conn, int id, CableSize cableSize1) throws SQLException {
        String sql = "UPDATE `hurricane_house`.`cable_size` SET `goods_id` = ?, `with` = ?, `height` = ?, `length` = ?, `voltage` = ?, `describe` = ? WHERE `id` = ?;";
        Object [] params = {cableSize1.getGoodsId(),cableSize1.getWith(),cableSize1.getHeight(),cableSize1.getLength(),cableSize1.getVoltage(),cableSize1.getDescribe(),id};
        return JDBCUtils.executeUpdate(conn,sql, params);
    }
}
